package iedcyt.acute.dataAccess.dao.acute1;

import iedcyt.acute.dataAccess.domain.acute1.Doc_Sign;
import iedcyt.acute.dataAccess.generic.SpringDao;
import iedcyt.acute.dataAccess.idao.acute1.IDoc_SignDao;

import java.util.ArrayList;
import java.util.List;



public class Doc_SignDao extends SpringDao implements IDoc_SignDao  {

	@Override
	public void create() {
		//comprobamos si la tabla existe y si no la creamos
		try {
			_jdbcTemplate.queryForInt("select count(*) from Doc_Sign");
		} catch (Exception e) {
			_jdbcTemplate.execute("CREATE TABLE Doc_Sign (idDocSign numeric, idDoc numeric, idSign numeric, signPosition numeric)");
			_jdbcTemplate.execute("CREATE INDEX idxDoc_Sign1 on Doc_Sign (idDocSign) WITH PRIMARY");
			_jdbcTemplate.execute("CREATE INDEX idxDoc_Sign2 on Doc_Sign (idDoc)");
			_jdbcTemplate.execute("CREATE INDEX idxDoc_Sign3 on Doc_Sign (idSign)");
			_jdbcTemplate.execute("CREATE UNIQUE INDEX idxDoc_Sign4 on Doc_Sign (idDocSign, SignPosition)");
			_jdbcTemplate.execute("ALTER TABLE Doc_Sign ADD FOREIGN KEY (idSign) REFERENCES Sign(idSign)");
			_jdbcTemplate.execute("ALTER TABLE Doc_Sign ADD FOREIGN KEY (idDoc) REFERENCES Document(idDoc)");
		}
	}
	
	@Override
	public List<Doc_Sign> selectAll() {
		
		return _jdbcTemplate.query("SELECT * FROM Doc_Sign",
				new Doc_SignRowMapper());
	}
	
	@Override
	public void deleteAll() {
		
		_jdbcTemplate.update("DELETE from Doc_Sign");
	}

	

	@Override
	public void insert(Doc_Sign doc) {
	
		_jdbcTemplate.update("INSERT INTO Doc_Sign (idDocSign, idDoc, idSign, SignPosition) VALUES (?,?,?,?)",
				new Object[] { doc.get_idDocSign(), doc.get_idDoc(), doc.get_idSign(), doc.get_SignPosition() });
		
	}
	
	@Override
	public void update(Doc_Sign doc) {
	
		_jdbcTemplate.update("UPDATE Doc_Sign SET  idDoc= ?, idSign= ?, SignPosition = ? WHERE idDocSign = ?",
				new Object[] { doc.get_idDoc(), doc.get_idSign(), doc.get_SignPosition(), doc.get_idDocSign() });
		
	}
	
	@Override
	public void updateIdSign(int oldIdSign, int newIdSign) {
	
		_jdbcTemplate.update("UPDATE Doc_Sign SET idSign= ? WHERE idSign = ?",
				new Object[] { newIdSign, oldIdSign });
		
	}

	
	@Override
	public void delete(int id) {
	
		_jdbcTemplate.update("DELETE from Doc_Sign WHERE idDocSign=?",
				new Object[] { id  });
		
	}

	@Override
	public int selectMaxId() {
		return _jdbcTemplate.queryForInt("SELECT MAX(idDocSign) FROM Doc_Sign");
	}

	/**
	 * @see iedcyt.acute.dataAccess.idao.acute1.IDoc_SignDao#selectByIdDocSign(int[], boolean)
	 */
	@Override
	public List<Doc_Sign> selectByIdDocSign(int[] ids, Boolean rps_bool) {
		
		if (ids.length != 0)
		{
		String rps = "";
		
		if (rps_bool != null) {
			if (rps_bool)
				rps = "AND SignPosition = -1";
			else rps = "AND SignPosition <> -1";
		}
		
		String where = "idDocSign = ? ";
		int cnt = 1;
		for (int i = 0; i < ids.length -1 ; i++)
		{
			cnt++;
			where += "OR idDocSign = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < ids.length ; i++)
		{
			objs[i] = ids[i];
		}
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Sign WHERE " + where + rps,
				objs,
				new Doc_SignRowMapper());
		}
		else return new ArrayList<Doc_Sign>();
	}

	@Override
	public List<Doc_Sign> selectByIdSign(int[] ids, boolean withoutRps) {
		
		if (ids.length != 0)
		{
		String rps = "";
		
		if (withoutRps)
			rps = "AND SignPosition <> -1";
		
		String where = "idSign = ? ";
		int cnt = 1;
		for (int i = 0; i < ids.length -1 ; i++)
		{
			cnt++;
			where += "OR idSign = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < ids.length ; i++)
		{
			objs[i] = ids[i];
		}
		
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Sign WHERE " + where + rps,
				objs,
				new Doc_SignRowMapper());
	}
	else return new ArrayList<Doc_Sign>();
	}

	
	
	@Override
	public List<Doc_Sign> selectByIdDoc(int[] ids, Boolean rps) {
		
		if (ids.length != 0)
		{
		String rpsQue = "";
		
		if (rps != null) {
			if (rps)
				rpsQue = "AND SignPosition = -1";
			else rpsQue = "AND SignPosition <> -1";
		}
		
		String where = "idDoc = ? ";
		int cnt = 1;
		for (int i = 0; i < ids.length -1 ; i++)
		{
			cnt++;
			where += "OR idDoc = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < ids.length ; i++)
		{
			objs[i] = ids[i];
		}
		
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Sign WHERE " + where + rpsQue,
				objs,
				new Doc_SignRowMapper());
	}
	else return new ArrayList<Doc_Sign>();
	}

	@Override
	public List<Doc_Sign> selectByIdDocAndSignPos(int idDoc, int signPos) {
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Sign WHERE idDoc = ? AND SignPosition = ?",
				new Object[] { idDoc, signPos },
				new Doc_SignRowMapper());
	}

	@Override
	public List<Doc_Sign> selectByIdDocAndIdSign(int[] idDocs, int idSign) {
		
		if (idDocs.length != 0)
		{
		String where = "idDoc = ? ";
		int cnt = 1;
		for (int i = 0; i < idDocs.length -1 ; i++)
		{
			cnt++;
			where += "OR idDoc = ? ";
		}
		where += "AND idSign = ? ";
		
		Object[] objs = new Object[cnt+1];
		
		for (int i = 0; i < idDocs.length ; i++)
		{
			objs[i] = idDocs[i];
		}
		
		objs[idDocs.length] = idSign;
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Sign WHERE " + where,
				objs,
				new Doc_SignRowMapper());
	}
	else return new ArrayList<Doc_Sign>();
	}
	
	@Override
	public List<Doc_Sign> selectNonClassify(int idDoc)
	{
		return _jdbcTemplate
		.query(
	"SELECT docS.idDocSign, docS.idDoc, docS.idSign, docS.SignPosition FROM Doc_Sign docS " + 
	"LEFT OUTER JOIN SignClassification sC ON docS.idDocSign = sC.idDocSign " +  
	"WHERE docS.idDoc = ? AND sC.idDocSign IS NULL ",// + 
	//"GROUP BY docS.idDocSign, docS.idDoc, docS.idSign, docS.SignPosition HAVING COUNT(docS.idSign) = 1",
	new Object[] { idDoc },
	new Doc_SignRowMapper());
	}
}

